<?php
namespace Pig\Controller;
use \Pig\Util\Page;

class FinanceController extends BaseController {

    
    public $tbName = 'Order';
    /*
     * 列表页面
     */
    public function index() {
        $Obj = D($this->tbName.' as a'); //实例化对象
        $Where = "a.deleted=0";
         /*关键字查询*/   
        if (I('request.keyword')!='') {
            $I_keyword = trim(I('request.keyword'));
            $Where .= " and (a.order_num like '%" . $I_keyword . "%' OR a.phone LIKE '%".$I_keyword."%' OR a.name LIKE '%".$I_keyword."%')";
        }
         /*订单状态查询*/   
        if (I('request.status')!='') {
            $I_keyword = trim(I('request.status'));
            $Where .= " and (a.status = {$I_keyword})";
        }
        /*开始，结束时间查询*/
        if(I('request.start') && !I('request.end')){
            $Where .= " and a.pay_time > ".strtotime(I('request.start').' 00:00:00');
        }else if(!I('request.start') && I('request.end')){
            $Where .= " and a.pay_time < ".strtotime(I('request.endime').' 23:59:59');
        }else if(I('request.start') && I('request.end')){
            $Where .= " and a.pay_time between ".strtotime(I('request.start').' 00:00:00')." and ".strtotime(I('request.end').' 23:59:59');
        }
        //控制分页显示条数
        if(I('post.limit_num')!=''){
            session('page_limit_num', I('post.limit_num'));
        }
        //控制列表排序
        $sorting = I('get.sorting') ? I('get.sorting') : 'a.id';
        $order = I('get.order') ? I('get.order') : 'desc';
        
        $limit_num = $_SESSION['page_limit_num'] ? $_SESSION['page_limit_num'] : 20;
        
        $Page = new Page($list_sum = $Obj->where($Where)->count(), $offset = $limit_num); // 实例化分页类 传入总记录数和每页显示的记录数
        $list = $Obj
                ->where($Where)
                ->order($sorting.' '.$order)
                ->limit($Page->firstRow . ',' . $Page->listRows)
                ->select();
        $Group = M('group');
        foreach($list as $key => &$value){
            if($Group->where(array('id' => $value['group_id']))->getField('type') == 3){
                $value['commission'] = $value['pay_price'] - $value['num']*$value['distribution_price'] - $value['postage_money'];
            }else{
                $value['commission'] = '--';
            }
        }
        $this->assign('list', $list); // 赋值数据集
        $this->assign('page', $Page->show()); // 分页显示输出
        $this->display(); // 输出模板
    }

    // Excel商品数据导出
    public function export(){
        try{
            import("Excel.PHPExcel");
            $Obj = D($this->tbName);
            $list = $Obj
                    ->where(array('deleted' => 0))
                    ->order('crdate DESC')
                    ->select();
            //数据导出
            $Excel = new \PHPExcel();
            // 设置基本属性
            $Excel_pro = $Excel->getProperties();
            $Excel_pro->setCreator('admin') //设置创建者
                      ->setLastModifiedBy(date('Y-m-d H:i:s', time())) //设置时间
                      ->setTitle('财务数据') //设置标题
                      ->setSubject('导出') //备注
                      ->setDescription('财务数据导出'); //描述
                      //->setKeywords('商品') //关键字
                      //->setCategory('商品');//类别

            // sheet
            $Excel->setActiveSheetIndex(0);
            // sheet title
            $Excel->getActiveSheet(0)->setTitle('财务数据');

            $Excel->getActiveSheet()->setCellValue('A1', '商品id');
            $Excel->getActiveSheet()->setCellValue('B1', '商品名称');
            $Excel->getActiveSheet()->setCellValue('C1', '团购id');
            $Excel->getActiveSheet()->setCellValue('D1', '订单id');
            $Excel->getActiveSheet()->setCellValue('E1', '成交时间');
            $Excel->getActiveSheet()->setCellValue('F1', '实付金额');
            $Excel->getActiveSheet()->setCellValue('G1', '红包金额');
            $Excel->getActiveSheet()->setCellValue('H1', '商品成本');
            $Excel->getActiveSheet()->setCellValue('I1', '快递成本');
            $Excel->getActiveSheet()->setCellValue('J1', '利润');

            foreach($list as $k => $v){
                $k = $k + 2;
                $Excel->getActiveSheet()->setCellValue('A' . $k, $v['goods_id']);
                $Excel->getActiveSheet()->setCellValue('B' . $k, $v['goods_name']);
                $Excel->getActiveSheet()->setCellValue('C' . $k, $v['group_id']);
                $Excel->getActiveSheet()->setCellValue('D' . $k, $v['id']);
                $Excel->getActiveSheet()->setCellValue('E' . $k,date('Y-m-d H:i:s',$v['crdate']));
                $Excel->getActiveSheet()->setCellValue('F' . $k, $v['pay_price']);
                $Excel->getActiveSheet()->setCellValue('G' . $k, $v['coupon_money']);
                $Excel->getActiveSheet()->setCellValue('H' . $k, $v['cost_money'] * $v['num']);
                $Excel->getActiveSheet()->setCellValue('I' . $k, $v['postage_money']);
                $Excel->getActiveSheet()->setCellValue('J' . $k, ($v['pay_price'] - $v['coupon_money'] - $v['cost_money'] * $v['num']));
            }
            //写入

            $Excel_write = \PHPExcel_IOFactory::createWriter($Excel, 'Excel5');

            // 输出
            ob_end_clean();
            header('Pragma: public');
            header('Expires: 0');
            header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
            header('Content-Type:application/force-download');
            header('Content-Type:application/vnd.ms-execl;');
            header('Content-Type:application/octet-stream');
            header('Content-Type:application/download');
            header("Content-Disposition:attachment;filename=财务数据-".date('Y-m-d').".xls");
            header('Content-Transfer-Encoding:binary');
            $Excel_write->save('php://output'); 
        }catch(\Exception $e){
            debug($e->getmessage());
            $this->error('导出失败');
        }
    }
}

?>